<?php

namespace app\admin\controller;

use app\admin\library\Excel;
use app\admin\model\Billidfordzd;
use app\common\controller\Backend;
use think\db\exception\PDOException;
use think\exception\ValidateException;
use think\facade\Db;

/**
 * 对账单管理
 *
 */
class Dzd extends Backend
{
    /**
     * Dzd模型对象
     * @var \app\admin\model\Dzd
     */
    protected $model = null;
    protected $bill= null;
    protected $billidfordzd=null;
    protected $quickSearchField = ['ID'];

    protected $defaultSortField = 'ID,desc';
    protected $noNeedLogin      = ['*'];

    protected $preExcludeFields = [''];

    public function initialize()
    {
        parent::initialize();
        $this->model = new \app\admin\model\Dzd;
        $this->bill =new \app\admin\model\Bill();
        $this->billidfordzd=new Billidfordzd();
    }
    /**
     * 查看
     */
    public function indsex()
    {
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->param('select')) {
            $this->select();
        }

        list($where, $alias, $limit, $order) = $this->queryBuilder();
        $adminInfo          = $this->auth->getInfo();
        //应收0,应付1
        $type= $this->request->get('type')?:'0';
        $wheres='';
        if($adminInfo['id']!=1){
            $wheres="DZOP=$adminInfo[nickname]";
        }
        $res = $this->model
            ->field("dzd.CORP,dzd.DZDNO,
            SUM(CASE WHEN bill.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS RMB,
            SUM(CASE WHEN bill.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS USD,
            ROUND(SUM(bill.MONEY * currentrate.RATE),2) AS ZHRMB,
            dzd.DZOP,
            dzd.DZDATE")
            ->join('bill','dzd.DZDNO=bill.MARKNO')
            ->join('currentrate','bill.MONEYTYPE=currentrate.MONEYTYPE')
            ->alias($alias)
            ->where($where)
            ->where("dzd.BILLTYPE = $type ")
            ->where($wheres)
//            ->order($order)
            ->group("dzd.CORP,dzd.DZDNO,dzd.DZOP,dzd.DZDATE")
//            ->fetchSql()->select();
            ->paginate($limit);
//var_dump($res);die;
        $this->success('', [
            'list'   => $res->items(),
            'total'  => $res->total(),
            'remark' => get_route_remark(),
        ]);
    }
    /*
     * 生成对账单效验
     */
    public function generate_xy(){
       $BILLID= $this->request->get('BILLID');
       $MARKNO=$this->bill->where('BILLID','in',$BILLID)->where("MARKNO is not null")->column('BILLID');
       if($MARKNO){
           $this->error('选择的费用中有部分已生成对账单号，请取消后再生成对账单',count($BILLID));
        }
        $CHECKED=$this->bill->where('BILLID','in',$BILLID)->where("CHECKED=0")->column('BILLID');

        if($CHECKED){
            $this->error("选择的费用中有" .count($CHECKED)."票业务费用未审核，请审核后再生成对账单",$CHECKED);
        }
       $this->success('无错误',count($BILLID));
    }
    /*
    * 生成对账单
    */
    public function generate(){
        $get= $this->request->get();
        $adminInfo          = $this->auth->getInfo();
        $add=[];
//        $get['BILLID']=['YS20210324151908'];
//        $get['type']=0;
        foreach ($get['BILLID'] as $k=>$item) {
            $add[$k]=[
                'billid'=>$item,
                'userid'=>$adminInfo['id']
            ];
        }
        $result = false;
        Db::startTrans();
        try {

            $this->billidfordzd->where("userid='$adminInfo[id]'")->delete();
            $result=$this->billidfordzd->saveAll($add);

            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if($result){
            $sql="SET NOCOUNT ON ;declare @dzdnolist varchar(16)DECLARE @userid varchar(30),@ny varchar(6),@dzdlist varchar(500) EXEC CREATEDZD  '$adminInfo[id]','$get[ny]',@dzdnolist output select @dzdnolist as no" ;
            $no= Db::query($sql)[0]['no'];
            if($no){
                $this->success('生成成功',$no);
                }else{
                $this->error('生产失败');
            }
         }
        else{
            $this->error('插入失败');
        }
    }
    /**
     * 开票申请/应收审核界面 0应收1应付
     */
    public function index_invoicing()
    {
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->param('select')) {
            $this->select();
        }

        list($where, $alias, $limit, $order) = $this->queryBuilder();
        $adminInfo          = $this->auth->getInfo();
        $wheres='';
        if($adminInfo['id']!=1){
            $wheres=" DZOP=$adminInfo[nickname] and";
        }
        $get= $this->request->get('type');

            if($get==0){
                if(empty($where)) $wheres.="DZD.FPSQ=0";

                $field="DZD.DZDNO,DZD.AMEND,
		SUM(CASE WHEN BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS RMB,
		SUM(CASE WHEN BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS USD,
		ROUND(SUM(BILL.MONEY * CURRENTRATE.RATE),2) AS ZHRMB,
		DZD.DZOP,
		DZD.DZDATE,
	    DZD.FPSQ,DZD.FPSQR,DZD.FPSQDATE,DZD.ID";
                $group="DZD.DZDNO,DZD.AMEND,DZD.DZOP,DZD.DZDATE,DZD.FPSQ,DZD.FPSQR,DZD.FPSQDATE,DZD.ID";
            }else{
                if(empty($where))   $wheres.="   DZD.CHECKED=0";
                $field="DZD.DZDNO,DZD.AMEND,
		SUM(CASE WHEN BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS RMB,
		SUM(CASE WHEN BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS USD,
		ROUND(SUM(BILL.MONEY * CURRENTRATE.RATE),2) AS ZHRMB,
		DZD.DZOP,
		DZD.DZDATE,
	    DZD.CHECKED,DZD.CHECKER,DZD.CHECKDATE,DZD.ID";
                $group="DZD.DZDNO,DZD.AMEND,DZD.DZOP,DZD.DZDATE,DZD.CHECKED,DZD.CHECKER,DZD.CHECKDATE,DZD.ID";
            }


        $res = $this->model
            ->field($field)
            ->join('bill','dzd.DZDNO=bill.MARKNO')
            ->join('currentrate','bill.MONEYTYPE=currentrate.MONEYTYPE')
            ->alias('DZD')
            ->where($where)
            ->where($wheres)
            ->order($order)
            ->group("$group")
//            ->fetchSql()->select();
            ->paginate($limit);
//var_dump($res);die;
        $this->success('', [
            'list'   => $res->items(),
            'total'  => $res->total(),
            'remark' => get_route_remark(),
        ]);
    }

    /**
     * 提交发票/审核
     */
    public function examine(){
        $adminInfo          = $this->auth->getInfo();
        $get=$this->request->get();
        if($get['type']==0){
            $up=['FPSQ'=>1,'FPSQR'=>$adminInfo['nickname'],'FPSQDATE'=>date("Y-m-d",time())];
        }else{
            $up=['CHECKED'=>1,'CHECKER'=>$adminInfo['nickname'],'CHECKDATE'=>date("Y-m-d",time())];
        }
        $result=false;
        Db::startTrans();
        try {

            $result=$this->model->where('ID',$get['id'])->save($up);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result !== false) {
            $this->success(__('Update successful'));
        } else {
            $this->error(__('No rows updated'));
        }
    }

    /**
     * JOB列表
     */
    public function index_job(){
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->param('select')) {
            $this->select();
        }

        list($where, $alias, $limit, $order) = $this->queryBuilder();
        $adminInfo          = $this->auth->getInfo();
        $wheres='';
        if($adminInfo['id']!=1){
            $wheres=" JOBOP=$adminInfo[nickname]";
        }
        $res = $this->model
            ->field(" DZD.JOBNO,BILL.CORPCODE,BILL.BILLCLASS,
		SUM(CASE WHEN BILL.BILLTYPE='0' AND BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS YSRMB,
		SUM(CASE WHEN BILL.BILLTYPE='0' AND BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS YSUSD,
		SUM(CASE WHEN BILL.BILLTYPE='1' AND BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS YFRMB,
		SUM(CASE WHEN BILL.BILLTYPE='1' AND BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS YFUSD,		
		ROUND(SUM( (CASE WHEN BILL.BILLTYPE='0' THEN BILL.MONEY  ELSE - BILL.MONEY END) * BILL.RATE),2) AS ZHRMB,
		DZD.JOBOP,
		DZD.JOBDATE")
            ->join('bill BILL','DZD.JOBNO=BILL.JOBNO')
            ->withJoin($this->withJoinTable, $this->withJoinType)
            ->alias('DZD')
            ->where($wheres)
            ->where("DZD.JOBNO IS NOT NULL AND DZD.BILLTYPE='0'")
            ->where($where)
            ->group("DZD.JOBNO,DZD.JOBOP,DZD.JOBDATE,BILL.CORPCODE,BILL.BILLCLASS")
            ->order("JOBDATE DESC")
//            ->fetchSql()->select();
            ->paginate($limit);
//var_dump($res);die;
        $this->success('', [
            'list'   => $res->items(),
            'total'  => $res->total(),
            'remark' => get_route_remark(),
        ]);
    }
    /**
     * 单个JOB明细查看
     */
    public function one_job(){

        $job=$this->request->get('job');
//        $job='2104SHJL0010';
        $one=Db::query("SELECT TZ.TZID,TZ.BILLTYPE,
	TZ.SENDER,
	TZ.GOODSC,
	GOODS.ENAME,
	MAX(CONLOADPLAN.CNTRNO) AS CNTRNO,
	TZ.PORTLOAD,
	TZ.PORTDIS,
	SUM(CASE WHEN BILL.BILLTYPE='0' AND BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS YSRMB,
	SUM(CASE WHEN BILL.BILLTYPE='0' AND BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS YSUSD,
	SUM(CASE WHEN BILL.BILLTYPE='1' AND BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS YFRMB,
	SUM(CASE WHEN BILL.BILLTYPE='1' AND BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS YFUSD,		
	ROUND(SUM( BILL.MONEY * BILL.RATE),2) AS ZHRMB,
	MAX(CONLOADPLAN.ZHDATE) AS ZHDATE,
	MAX(CONLOADPLAN.SHDATE) AS SHDATE,
	TZ.SALER,
	TZ.SALERDEPT,
	TZ.OP,
	TZ.OPDEPT,
	TZ.OPTIME	
FROM tz as TZ ,bill as BILL, conloadplan as CONLOADPLAN,goods as GOODS
WHERE TZ.TZID=CONLOADPLAN.TZID AND TZ.TZID=BILL.TZID AND TZ.CONTRACTNO=GOODS.GOODSID AND BILL.JOBNO='$job'
GROUP BY TZ.TZID, 
	TZ.SENDER,
	TZ.GOODSC,
	GOODS.ENAME,	
	TZ.PORTLOAD,
	TZ.PORTDIS,	
	TZ.SALER,
	TZ.SALERDEPT,
	TZ.OP,
	TZ.OPDEPT,
	TZ.OPTIME");

      $two=Db::query("SELECT BILL.MARKNO,BILL.TZID,BILL.BILLTYPE,
	BILL.BILLCLASS,
	BILL.CORPCODE,
	BILL.ITEMCODE,
	BILL.MONEYTYPE,
	BILL.MONEY,
	BILL.TAXRATE,
	BILL.AMOUNT,
	BILL.PRICETYPE,
	BILL.RATIO,BILL.JOBNO		
FROM bill as BILL,codelib as CODELIB
WHERE BILL.ITEMCODE=CODELIB.CODE AND CODELIB.CODETYPE='itemcode'  AND BILL.JOBNO='$job'  ");
       //循环分组
        foreach ($one as $k=>$item) {
            foreach ($two as $t) {
                if($item['TZID']==$t['TZID']){
                    $one[$k]['two'][]=$t;
                }
            }
        }
        $this->success('', [
            'list'   => $one,
            'remark' => get_route_remark(),
        ]);
    }
    /**
     * 应收对账单列表
     */
    public function job_ys(){
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->param('select')) {
            $this->select();
        }

        list($where, $alias, $limit, $order) = $this->queryBuilder();
        $adminInfo          = $this->auth->getInfo();
        $wheres='';
        if($adminInfo['id']!=1){
            $wheres="AND JOBOP=$adminInfo[nickname]";
        }
        $res = $this->model
            ->field(" DZD.DZDNO,DZD.AMEND,DZD.CORP,
		(select count(distinct tz.tzid) from bill,tz where bill.TZID=tz.TZID and bill.MARKNO=DZD.DZDNO and tz.OVERSTATUS='1'  ) AS tjnum ,
		(select count(distinct tz.tzid) from bill,tz where bill.TZID=tz.TZID and bill.MARKNO=DZD.DZDNO ) AS totalnum,	
		SUM(CASE WHEN BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS RMB,
		SUM(CASE WHEN BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS USD,
		ROUND(SUM(BILL.MONEY * BILL.RATE),2) AS ZHRMB,
		DZD.DZOP,
		DZD.DZDATE")
            ->join('bill BILL',"DZD.DZDNO=BILL.MARKNO")
            ->join("tz TZ","TZ.TZID=BILL.TZID")
            ->withJoin($this->withJoinTable, $this->withJoinType)
            ->alias('DZD')
            ->where("DZD.BILLTYPE=0 AND BILL.JOBNO IS NULL $wheres")
            ->where($where)
            ->group("DZD.DZDNO,DZD.AMEND,DZD.DZOP,DZD.DZDATE,DZD.CORP")
            ->paginate($limit);

        $this->success('', [
            'list'   => $res->items(),
            'total'  => $res->total(),
            'remark' => get_route_remark(),
        ]);
    }

    /**
     * 应付业务列表
     */
    public function business(){
        $get=$this->request->get();
        if($get['AMEND']==0){
            $where=" AND  bill.billclass in ('0','1') ";
        }else{
            $where=" bill.billclass =2 ";
        }
        $one=Db::query("SELECT TZ.TZID,
                TZ.SENDER,
                TZ.GOODSC,
                GOODS.ENAME,
                MAX(CONLOADPLAN.CNTRNO) AS CNTRNO,
                TZ.PORTLOAD,
                TZ.PORTDIS,
                SUM(CASE WHEN BILL.MONEYTYPE='RMB' THEN BILL.MONEY ELSE 0 END) AS RMB,
                SUM(CASE WHEN BILL.MONEYTYPE='USD' THEN BILL.MONEY ELSE 0 END) AS USD,
                MAX(CONLOADPLAN.ZHDATE) AS  ZHDATE,
                MAX(CONLOADPLAN.SHDATE) AS  SHDATE,
                TZ.SALER,
                TZ.SALERDEPT,
                TZ.OP,
                TZ.OPDEPT,
                TZ.OPTIME	
            FROM TZ,BILL,CONLOADPLAN,GOODS
            WHERE TZ.TZID=CONLOADPLAN.TZID AND TZ.TZID=BILL.TZID AND TZ.CONTRACTNO=GOODS.GOODSID AND BILL.BILLTYPE='1' /*应付*/
                    AND EXISTS ( SELECT * FROM BILL A WHERE TZ.TZID=A.TZID AND A.MARKNO='$get[DZDNO]')
                    AND BILL.JOBNO IS NULL	
                    $where
            GROUP BY TZ.TZID, 
                TZ.SENDER,
                TZ.GOODSC,
                GOODS.ENAME,
                TZ.PORTLOAD,
                TZ.PORTDIS,	
                TZ.SALER,
                TZ.SALERDEPT,
                TZ.OP,
                TZ.OPDEPT,
                TZ.OPTIME
            ");
        $two=Db::query("SELECT BILL.BILLCLASS,
	BILL.CORPCODE,
	BILL.ITEMCODE,
	BILL.MONEYTYPE,
	BILL.MONEY,
	BILL.TAXRATE,
	BILL.AMOUNT,
	BILL.PRICETYPE,
	BILL.RATIO,
	BILL.OPNAME,
	BILL.OPDATETIME,
	BILL.TZID	
FROM BILL,CODELIB
WHERE BILL.ITEMCODE=CODELIB.CODE AND CODELIB.CODETYPE='itemcode'  AND BILL.BILLTYPE='1' AND EXISTS ( SELECT * FROM BILL A WHERE BILL.TZID=A.TZID AND A.MARKNO='$get[DZDNO]') AND BILL.JOBNO IS NULL $where");
        //循环分组
        foreach ($one as $k=>$item) {
            foreach ($two as $t) {
                if($item['TZID']==$t['TZID']){
                    $one[$k]['two'][]=$t;
                }
            }
        }
        $this->success('', [
            'list'   => $one,
            'remark' => get_route_remark(),
        ]);
    }

    /**
     * 仅应付更正单列表
     */
    public function corrections(){
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->param('select')) {
            $this->select();
        }

        list($where, $alias, $limit, $order) = $this->queryBuilder();
        $adminInfo          = $this->auth->getInfo();
        $wheres='';
        if($adminInfo['id']!=1){
            $wheres="AND JOBOP=$adminInfo[nickname]";
        }
        $res = $this->model
            ->field("DZD.DZDNO,DZD.AMEND,
		SUM(CASE WHEN BILL.MONEYTYPE='RMB' THEN MONEY ELSE 0 END) AS RMB,
		SUM(CASE WHEN BILL.MONEYTYPE='USD' THEN MONEY ELSE 0 END) AS USD,
		ROUND(SUM(BILL.MONEY * CURRENTRATE.RATE),2) AS ZHRMB,
		DZD.DZOP,
		DZD.DZDATE")
            ->join('bill BILL',"DZD.DZDNO=BILL.MARKNO")
            ->join("tz TZ","TZ.TZID=BILL.TZID")
            ->join("currentrate CURRENTRATE","BILL.MONEYTYPE=CURRENTRATE.MONEYTYPE")
            ->withJoin($this->withJoinTable, $this->withJoinType)
            ->alias('DZD')
            ->where("DZD.BILLTYPE=1 AND DZD.AMEND='1' AND BILL.JOBNO IS NULL $wheres")
            ->where($where)
            ->group("DZD.DZDNO,DZD.AMEND,DZD.DZOP,DZD.DZDATE")
            ->paginate($limit);

        $this->success('', [
            'list'   => $res->items(),
            'total'  => $res->total(),
            'remark' => get_route_remark(),
        ]);
    }

    /**
     * 更正的应付业务列表应付费用列表
     */
    public function corrections_list(){
        $get=$this->request->get();

        $one=Db::query("SELECT TZ.TZID,
	TZ.SENDER,
	TZ.GOODSC,
	GOODS.ENAME,
	CONLOADPLAN.CNTRNO,
	TZ.PORTLOAD,
	TZ.PORTDIS,
	SUM(CASE WHEN BILL.MONEYTYPE='RMB' THEN BILL.MONEY ELSE 0 END) AS RMB,
	SUM(CASE WHEN BILL.MONEYTYPE='USD' THEN BILL.MONEY ELSE 0 END) AS USD,
	CONLOADPLAN.ZHDATE,
	CONLOADPLAN.SHDATE,
	TZ.SALER,
	TZ.SALERDEPT,
	TZ.OP,
	TZ.OPDEPT,
	TZ.OPTIME	
FROM TZ,BILL,CONLOADPLAN,GOODS
WHERE TZ.TZID=CONLOADPLAN.TZID AND TZ.TZID=BILL.TZID AND TZ.CONTRACTNO=GOODS.GOODSID AND BILL.BILLTYPE='1' /*应付*/
		AND BILL.MARKNO='$get'
		AND BILL.JOBNO IS NULL	

GROUP BY TZ.TZID, 
	TZ.SENDER,
	TZ.GOODSC,
	GOODS.ENAME,
	CONLOADPLAN.CNTRNO,
	TZ.PORTLOAD,
	TZ.PORTDIS,	
	CONLOADPLAN.ZHDATE,
	CONLOADPLAN.SHDATE,
	TZ.SALER,
	TZ.SALERDEPT,
	TZ.OP,
	TZ.OPDEPT,
	TZ.OPTIME");
        $two=Db::query("SELECT BILL.BILLCLASS,BILL.TZID,
	BILL.CORPCODE,
	BILL.ITEMCODE,
	BILL.MONEYTYPE,
	BILL.MONEY,
	BILL.TAXRATE,
	BILL.AMOUNT,
	BILL.PRICETYPE,
	BILL.RATIO,
	BILL.OPNAME,
	BILL.OPDATETIME
FROM BILL,CODELIB
WHERE BILL.ITEMCODE=CODELIB.CODE AND CODELIB.CODETYPE='itemcode'  AND BILL.BILLTYPE='1'   AND  BILL..MARKNO='$get'  AND BILL.JOBNO IS NULL");
        //循环分组
        foreach ($one as $k=>$item) {
            foreach ($two as $t) {
                if($item['TZID']==$t['TZID']){
                    $one[$k]['two'][]=$t;
                }
            }
        }
        $this->success('', [
            'list'   => $one,
            'remark' => get_route_remark(),
        ]);

}

    /**
     * 生成job号
     */
    public function add_job(){
        $dzno=$this->request->get('dzno');
        $dzno='2021DZYS0004';
        $adminInfo          = $this->auth->getInfo();
        $sql="SET NOCOUNT ON ;DECLARE @jobno VARCHAR(30) EXEC CREATEJOB  $adminInfo[id],'$dzno',@jobno output select @jobno as no" ;
        $no=Db::query($sql)[0]['no'];
        if($no!='没有找到对应的正常账单的JOB号' or $no!='无法生成AMENDMENT账单JOB号'){
            $this->success('成功');
        }else{
            $this->error($no);
        }
    }
    public function print_z(){
        $get=$this->request->get();
        if($get['type']==1){
            $type='printlbl_1kxyswts';
        }elseif ($get['type']==2){
            $type='printlbl_2kxjctz';
        }elseif ($get['type']==3){
            $type='printlbl_3txtzcknm';
        }elseif ($get['type']==4){
            $type='printlbl_4txtzjr';
        }elseif ($get['type']==5){
            $type='printlbl_5txtznmbqx';
        }elseif ($get['type']==6){
            $type='printlbl_6txtznmtb';
        }elseif ($get['type']==7){
            $type='printlbl_7nmzxhgzd';
        }elseif ($get['type']==8){
            $type='printlbl_8cknmzhgzd';
        }elseif ($get['type']==9){
            $type='printlbl_9nmzhgzd';
        }elseif ($get['type']==10){
            $type='printlbl_10nmjkxhgzd';
        }elseif ($get['type']==11){
            $type='printlbl_11nmxhgzd';
        }elseif ($get['type']==12){
            $type='printjobsheet';
        }elseif ($get['type']==13){
            $type='previewjobsheet';
        }elseif ($get['type']==14){
            $type='printjobsheet_tzid';
        }elseif ($get['type']==15){
            $type='printamendment';
        }elseif ($get['type']==16){
            $type='previewamendment';
        }
        $data=$this->print_s($type,$get['tzid']);
        $this->success('成功',$data);
    }
    /**
     *导出格式列表
     */
    public function out_list(){
        $corp=$this->request->get('CORP');
        $gs=Db::table('DZDGS')->where('DZDGS.CORP',$corp)->select();
        $this->success('',$gs);
    }

    /**
     * 导出
     */
    public function out(){
        $get=$this->request->get();
//        $get['id']=7;
//        $get['no']="2023DZYS0006";
        $tail=Db::table('DZDGSDETAIL')->where('GSID',$get['id'])->select()->toArray();
        $field=[];
        foreach ($tail as $k=>$v) {
            $field[$k]=$v['TABLENAME'].'.'.$v['COLNAME'];
        }
        $fields=implode(',',$field);
        $data=Db::table('TZ')->field($fields)
            ->join('BILLVIEW','TZ.TZID=BILLVIEW.TZID')
            ->join('GOODS','TZ.CONTRACTNO=GOODS.GOODSID')
            ->join('CONLOADPLAN','TZ.TZID=CONLOADPLAN.TZID')
            ->where('BILLVIEW.MARKNO',$get['no'])
//            ->fetchSql()
            ->select()
            ->toArray();
//        var_dump($data);die;
        $head=array_column($tail,'COLLABEL');
        $key=array_column($tail,'COLNAME');
//        foreach ($data as &$v) {
//            $v=array_values($v);
//        }
        // 调用类库，读取excel中的内容
//        $a=Excel::export($head,'',$data,'对账单导出');
       Excel::downloadExcel('对账单导出',$data,$head,$key);

    }
}